﻿using STHPMS1.DAL.D_Core;
using System.Data;
using ServiceStack.OrmLite;
using STHPMS1.ICore.I_Entity.Stmpms1;
using STHPMS1.Common.C_Entity.Stmpms1;
using STHPMS1.ICore.I_DAL.Repository;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace STHPMS1.DAL.D_Repository
{
    public class B_InvntoryRepository : DalBase, IB_InvntoryRepository
    {
        public DataTable  GetOrderByList(string rdwayid, int dir)
        {
            using(SqlConnection conn = new SqlConnection(Adapter.ConnectionString))
            {
                DataTable dt = new DataTable();
                string sql = string.Format("select * from B_INVNTORY where RDWAYID='{0}' and DIRECTION_DICTID={1} order by ENDMP",rdwayid,dir);
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(dt);
                return dt;
            }
        }

        public int GetRdclass_Dictid(string unit)
        {
            IDbConnection conn = Adapter.OpenDbConnection();
            //return conn.Scalar<int>("select B_INVNTORY.RDCLASS_DICTID from B_INVNTORY left join B_UNIT on B_INVNTORY.SEGID=B_UNIT.SEGID where B_UNIT.UNIT='" + unit + "'");

            return conn.Scalar<int>("select B_INVNTORY.RDCLASS_DICTID  from B_INVNTORY left join B_INVNTORY_UNIT on B_INVNTORY.SEGID=B_INVNTORY_UNIT.SEGID where B_INVNTORY_UNIT.UNIT='"+unit+"'");
           
        }

        public int GetRirection_Dictid(string unit)
        {
            IDbConnection conn = Adapter.OpenDbConnection();
            //return conn.Scalar<int>("select B_INVNTORY.DIRECTION_DICTID from B_INVNTORY left join B_UNIT on B_INVNTORY.SEGID=B_UNIT.SEGID where B_UNIT.UNIT='" + unit + "'");

            return conn.Scalar<int>("select B_INVNTORY.DIRECTION_DICTID  from B_INVNTORY left join B_INVNTORY_UNIT on B_INVNTORY.SEGID=B_INVNTORY_UNIT.SEGID where B_INVNTORY_UNIT.UNIT='"+unit+"'");
            
        }

        public string GetSegID(string unit)
        {
            IDbConnection conn = Adapter.OpenDbConnection();
            return conn.Scalar<string>("select B_INVNTORY.SEGID from B_INVNTORY left join B_INVNTORY_UNIT on B_INVNTORY_UNIT.SEGID=B_INVNTORY.SEGID where B_INVNTORY_UNIT.UNIT='" + unit + "'");
        }

        public DataTable GetRoadDivisionResult()
        {
            string sql = @"
SELECT a.Rdwayid,a.Name,b.SegCount,c.UnitCount,
				CASE 
						WHEN (e.HIS_VERSION is NULL) THEN 0 
						WHEN (a.HIS_VERSION is NULL) THEN 1 
						WHEN (a.HIS_VERSION<>e.HIS_VERSION) THEN 1 
						ELSE 0 
				END as Change_Ornot FROM 
	B_ROADWAY a
LEFT JOIN  
	(SELECT RDWAYID,COUNT(SEGID) as SegCount FROM B_INVNTORY GROUP BY(RDWAYID)) b
		ON a.RDWAYID=b.RDWAYID
LEFT JOIN 
	(SELECT RDWAYID,Count(UNIT) as UnitCount FROM B_UNIT GROUP BY(RDWAYID)) c
		ON a.RDWAYID=c.RDWAYID
LEFT JOIN
	(
		SELECT RDWAYID,MAX(HIS_VERSION) as HIS_VERSION
		From(
			SELECT  RDWAYID ,HIS_VERSION FROM B_RDSTANDARD
			UNION 
			SELECT  RDWAYID ,HIS_VERSION FROM B_STURCTURE
			UNION 
			SELECT  RDWAYID as a,HIS_VERSION as b FROM B_LAYER
			UNION 
			SELECT  RDWAYID ,HIS_VERSION FROM B_PMSTC) d
		GROUP BY d.RDWAYID
		) e
		on a.RDWAYID=e.RDWAYID ";

            return this.Adapter.DataAdapter.FillDataTable(sql);
        }

        public double GetNewVersion(string rdwayid)
        {
            string sql = string.Format(@"SELECT MAX(HIS_VERSION) as HIS_VERSION
		From(
			SELECT  RDWAYID ,HIS_VERSION FROM B_RDSTANDARD where RDWAYID='{0}' 
			UNION 
			SELECT  RDWAYID ,HIS_VERSION FROM B_STURCTURE where RDWAYID='{0}' 
			UNION 
			SELECT  RDWAYID as a,HIS_VERSION as b FROM B_LAYER where RDWAYID='{0}' 
			UNION 
			SELECT  RDWAYID ,HIS_VERSION FROM B_PMSTC where RDWAYID='{0}' ) d 
		GROUP BY d.RDWAYID", rdwayid);


            IDbConnection conn = Adapter.OpenDbConnection();
            return conn.Scalar<double>(sql);
        }
    }
}
